﻿CREATE procedure [dbo].[spSaveClientVariance]
(
	@year int
	, @date datetime
	, @ReferenceDate date
)
as

set ansi_nulls OFF

DECLARE @lastDatePrevious date, @yearText varchar(4), @weekNumber varchar(4), @dayOfWeek varchar(4), @dateText varchar(50), @lastDatePreviousText varchar(50)

DECLARE @query nvarchar(max)
SET @yearText = CONVERT(varchar(4), @year);
SET @dateText = CONVERT(varchar(50), @date);

set @query = N'select @lastDatePrevious = ISNULL(MAX(ReferenceDate),''' +  CONVERT(varchar(30), @date) + ''' ) from BookOfWork' 
	+ @yearText + ' WHERE ReferenceDate < ''' + @dateText + ''''

exec sp_executesql @query, N'@lastDatePrevious date output', @lastDatePrevious output;

SET @weekNumber =  CONVERT(varchar(4),datepart(day, datediff(day, 0, @date)/7 * 7)/7 + 1)
SET @dayOfWeek =  CONVERT(varchar(4), DATEPART(weekday, @date))
SET @lastDatePreviousText = CONVERT(varchar(50), @lastDatePrevious);

SET @query = 
'MERGE BookOfWorkClientVariance AS Target
USING 
(
	SELECT DISTINCT 
	ISNULL(currentBow.ProgramId, previousBow.ProgramId) as [ProgramId]
	, ISNULL(currentBow.ProgramName, previousBow.ProgramName) as [ProgramName]
	, ISNULL(currentBow.ProgramManager, previousBow.ProgramManager) as [ProgramManager]
	, ISNULL(currentBow.ProjectId, previousBow.ProjectId) as [ProjectId]
	, ISNULL(currentBow.ProjectName, previousBow.ProjectName) as [ProjectName]
    , ISNULL(currentBow.BowFlag, previousBow.BowFlag) as [BowFlag]
	, ISNULL(currentBow.Client5Name, previousBow.Client5Name) as [ClientLevel5]
	, ISNULL(currentBow.Client6Name, previousBow.Client6Name) as [ClientLevel6]
	, ISNULL(currentBow.Client7Name, previousBow.Client7Name) as [ClientLevel7]
	, ClientGOC.GocAttributes as [ClientGocCode]
    , ISNULL(currentBow.LobParent, previousBow.LobParent) as [LobParent]
	--, CONVERT(DECIMAL(38,2), SUM(ISNULL(previousBow.[ProjectClientGocSum], 0))) as [FY0]
	--, CONVERT(DECIMAL(38,2), SUM(ISNULL(previousBow.ClientAllocation, 0))) as [Date0]
	--, CONVERT(DECIMAL(38,2), SUM(ISNULL(currentBow.[ProjectClientGocSum], 0))) as [FY1]
	--, CONVERT(DECIMAL(38,2), SUM(ISNULL(currentBow.ClientAllocation, 0))) as [Date1]
	, CONVERT(DECIMAL(38,2), (ISNULL(previousBow.[ProjectClientGocSum], 0))) as [FY0]
	, CONVERT(DECIMAL(38,2), (ISNULL(previousBow.ClientAllocation, 0))) as [Date0]
	, CONVERT(DECIMAL(38,2), (ISNULL(currentBow.[ProjectClientGocSum], 0))) as [FY1]
	, CONVERT(DECIMAL(38,2), (ISNULL(currentBow.ClientAllocation, 0))) as [Date1]
from BookOfWork' + @yearText + ' as currentBow
	FULL OUTER JOIN BookOfWork' + @yearText + ' previousBow on previousBow.InitiativeId = currentBow.InitiativeId
		AND previousBow.ProgramId = currentBow.ProgramId
		AND previousBow.ProjectId = currentBow.ProjectId
		AND previousBow.ClientGocCode = currentBow.ClientGocCode
		AND previousBow.ReferenceDate = ''' + @lastDatePreviousText + '''  
	INNER JOIN GOC as ClientGOC on (ClientGOC.GocAttributes = currentBow.ClientGocCode OR ClientGOC.GocAttributes = previousBow.ClientGocCode)
		AND ClientGOC.ManagedGeography = ''BR''
	Where currentBow.ReferenceDate = ''' + @dateText + '''  
	AND CONVERT(DECIMAL(38,0), ISNULL(currentBow.ClientAllocation, 0)) - CONVERT(DECIMAL(38,0), ISNULL(previousBow.ClientAllocation, 0)) <> 0
	AND (currentBow.ProgramName IS NOT NULL OR previousBow.ProgramName IS NOT NULL)
	AND ISNULL(currentBow.[ProjectClientGocSum], 0) - ISNULL(previousBow.ProjectClientGocSum, 0) <> 0
--GROUP BY
--	ISNULL(currentBow.ProgramId, previousBow.ProgramId)
--	, ISNULL(currentBow.ProgramName, previousBow.ProgramName)
--	, ISNULL(currentBow.ProgramManager, previousBow.ProgramManager) 
--	, ISNULL(currentBow.ProjectId, previousBow.ProjectId)
--	, ISNULL(currentBow.ProjectName, previousBow.ProjectName)
--  , ISNULL(currentBow.BowFlag, previousBow.BowFlag) 
--	, ISNULL(currentBow.Client5Name, previousBow.Client5Name)
--	, ISNULL(currentBow.Client6Name, previousBow.Client6Name)
--	, ISNULL(currentBow.Client7Name, previousBow.Client7Name)
--	, ClientGOC.GocAttributes
--  , ISNULL(currentBow.LobParent, previousBow.LobParent) 
--HAVING CONVERT(DECIMAL(38,2), SUM(ISNULL(currentBow.ClientAllocation, 0))) - CONVERT(DECIMAL(38,2),SUM( ISNULL(previousBow.ClientAllocation, 0))) <> 0
--	AND SUM(ISNULL(currentBow.[ProjectClientGocSum], 0)) - SUM(ISNULL(previousBow.ProjectClientGocSum, 0)) <> 0
) 
AS Source
ON 
(
	Target.ProgramId = Source.ProgramId
	AND Target.ProjectId = Source.ProjectId
	AND Target.ClientGocCode = Source.ClientGocCode
	AND Target.LobParent = Source.LobParent
	AND Target.PeriodYear = ' + @yearText + ' 
	AND Target.WeekNumber = ' + @weekNumber + '
	AND Target.ReferenceDate = ''' + CONVERT(varchar(50), @ReferenceDate) + ''' 
	AND Target.[Month] = ' + CONVERT(VARCHAR(2), Month(@date)) + '
)
WHEN MATCHED THEN
    UPDATE SET 
			Target.PLFY0 = CASE ' + @dayOfWeek + ' 
				WHEN 2 THEN Source.FY0 
			END 
			,Target.Allocation0 = CASE ' + @dayOfWeek + ' 
				WHEN 2 THEN Source.Date0 
			END
			,Target.PLFY1 = CASE ' + @dayOfWeek + ' 
				WHEN 3 THEN Source.FY0 
				WHEN 2 THEN Source.FY1 
			END
			,Target.Allocation1 = CASE ' + @dayOfWeek + ' 
				WHEN 3 THEN Source.Date0 
				WHEN 2 THEN Source.Date1 
			END
			,Target.PLFY2 = CASE ' + @dayOfWeek + ' 
				WHEN 4 THEN Source.FY0 
				WHEN 3 THEN Source.FY1 
			END
			,Target.Allocation2 = CASE ' + @dayOfWeek + ' 
				WHEN 4 THEN Source.Date0 
				WHEN 3 THEN Source.Date1 
			END
			,Target.PLFY3 = CASE ' + @dayOfWeek + ' 
				WHEN 5 THEN Source.FY0 
				WHEN 4 THEN Source.FY1 
			END
			,Target.Allocation3 = CASE ' + @dayOfWeek + ' 
				WHEN 5 THEN Source.Date0 
				WHEN 4 THEN Source.Date1 
			END
			,Target.PLFY4 = CASE ' + @dayOfWeek + ' 
				WHEN 6 THEN Source.FY0 
				WHEN 5 THEN Source.FY1 
			END
			,Target.Allocation4 = CASE ' + @dayOfWeek + ' 
				WHEN 6 THEN Source.Date0 
				WHEN 5 THEN Source.Date1 
			END
			,Target.PLFY5 = CASE ' + @dayOfWeek + '
				WHEN 6 THEN Source.FY1 
			END
			,Target.Allocation5 = CASE ' + @dayOfWeek + '
				WHEN 6 THEN Source.Date1 
			END
WHEN NOT MATCHED BY TARGET THEN
    INSERT 
    VALUES 
		(
			' + @yearText + '
			,' + @weekNumber + '
			,' + CONVERT(VARCHAR(2), Month(@date)) + '
			,''' + CONVERT(varchar(50), @ReferenceDate) + '''  
			,Source.ProgramId 
			,Source.ProgramName 
			,Source.ProgramManager 
			,Source.ProjectId 
			,Source.ProjectName 
			,Source.BowFlag 
			,Source.ClientLevel5
			,Source.ClientLevel6
			,Source.ClientLevel7
			,Source.ClientGocCode 
			,Source.LobParent 
			,CASE ' + @dayOfWeek + ' 
				WHEN 2 THEN Source.FY0 
			END 
			,CASE ' + @dayOfWeek + ' 
				WHEN 2 THEN Source.Date0 
			END
			,CASE ' + @dayOfWeek + ' 
				WHEN 3 THEN Source.FY0 
				WHEN 2 THEN Source.FY1 
			END
			,CASE ' + @dayOfWeek + ' 
				WHEN 3 THEN Source.Date0 
				WHEN 2 THEN Source.Date1 
			END
			,CASE ' + @dayOfWeek + ' 
				WHEN 4 THEN Source.FY0 
				WHEN 3 THEN Source.FY1 
			END
			,CASE ' + @dayOfWeek + ' 
				WHEN 4 THEN Source.Date0 
				WHEN 3 THEN Source.Date1 
			END
			,CASE ' + @dayOfWeek + ' 
				WHEN 5 THEN Source.FY0 
				WHEN 4 THEN Source.FY1 
			END
			,CASE ' + @dayOfWeek + ' 
				WHEN 5 THEN Source.Date0 
				WHEN 4 THEN Source.Date1 
			END
			,CASE ' + @dayOfWeek + ' 
				WHEN 6 THEN Source.FY0 
				WHEN 5 THEN Source.FY1 
			END
			,CASE ' + @dayOfWeek + ' 
				WHEN 6 THEN Source.Date0 
				WHEN 5 THEN Source.Date1 
			END
			,CASE ' + @dayOfWeek + '
				WHEN 6 THEN Source.FY1 
			END
			,CASE ' + @dayOfWeek + '
				WHEN 6 THEN Source.Date1 
			END
		); '

EXEC (@query)